Information retrieval system and method using index ANDing for improving performance

ABSTRACT

An information retrieval system is adapted to process a query having an associated plan that includes applying predicates comprising local predicates and a join predicate, to at least two tables. Index ANDing is used to construct a filter for filtering a first of the tables in accordance with the local predicates. The join predicate is applied to the tables for determining row identifiers of rows satisfying the join predicate. The row identifiers are used to probe the filter for determining whether the rows satisfying the join predicate also satisfy the local predicates. The rows that satisfy both the join and local predicates are fetched and the join predicate is completed. Results of the join predicate are then returned.

FIELD OF THE INVENTION

The present invention relates to information retrieval systems ingeneral. In particular, the present invention relates to informationretrieval systems using index ANDing (IXA) for improving performance.

BACKGROUND OF THE INVENTION

A common form of information retrieval system for managing computerizedrecords contained in a database is a relational database managementsystem. Between an actual database (that is, data stored for use by acomputer) and users of the contents of that database is a software layerknown as the relational database management system (RDBMS or DBMS). TheDBMS is responsible for handling all requests for access to the databaseand shielding the users from the details of any specific hardware and/orsoftware implementation. Using relational techniques, the DBMS stores,manipulates and retrieves data in table form. Typically, theserelationships are defined by a set of columns and a set of rows. Thecolumns are also referred to as attributes, or data types. The rows arealso referred to as records or tuples, of data.

A standard language for dealing with DBMSs is the Structured QueryLanguage (SQL). SQL comprises both data definition operations and datamanipulation operations. To maintain data independence, a set of SQLcommands, referred to as a query, provides the DBMS with instructions toperform. However, the query does not describe directions for performingthe instructions. Thus, the DBMS comprises a query processor forgenerating various query plans of execution and choosing a preferredplan. Due to the high-level nature of relational expressions and avariety of implementation techniques, automatic query optimization ispossible and often necessary to ensure efficient query processing.

In accordance with well-known query translation processes, an SQL queryis processed in stages. The initial stage casts the source query into aninternal form such as the Query Graph Model (QGM) following thepreliminary steps of lexing, parsing and semantic checking. The goal ofthe QGM is to provide a more powerful and conceptually more manageablerepresentation of queries to reduce the complexity of query compilationand optimization. The internal QGM is a data structure for providing thesemantic relationships of the query for use by query translator andoptimizer components for rewriting the query in a canonical form. In anext phase, a plan optimizer produces a query execution plan such as bygenerating alternate plans and choosing a best plan based on estimatedexecution costs. A plan refinement stage may be employed to refine theoptimum execution plan in accordance with run-time requirements.

Accessing a table of data for carrying out a query typically requirestransferring all or part of the table to random access memory (RAM) ofthe computer. When information is needed from a plurality of tables, thetables may be joined by the DBMS. Join operations, referred to hereafteras joins, allow information to be obtained across tables in a meaningfulway.

One such join is a nested loop join (NLJN). The NLJN selects one tableas an outer table and the other as the inner table. When executing anested-loop join, the outer table is scanned row by row. For each row inthe outer table, the inner table is scanned looking for matching rows. ANLJN is efficient if one of the tables is small, or has been filteredsuch that it has only a few qualifying rows, and the other table has anindex on the column that joins the tables. Typically, the smaller tableis selected as the outer table because it is the fastest method ofexecuting the NLJN.

FIG. 1 a illustrates an exemplary first table T1 and a second table T2respectively. The first table T1 comprises four (4) columns C1, C2, C3,and C4 and six (6) rows, R1, R2, R3, R4, R5, and R6. Similarly, thesecond table T2 comprises four (4) columns D1, D2, D3, and D4 and eight(8) rows, P1, P2, P3, P4, P5, P6, P7, and P8. A query 102 requirescombining the rows of tables T1 and T2 as follows:Select * from T1, T2 where T1.C1=T2.D1 and T2.D2=7 and T2.D3=9   (1)

FIG. 1 b illustrates exemplary table indices for the second table T2. Afirst index T2.idx.D1 represents the second table T2 indexed by columnD1. A second index T2.idx.D2 represents the second table T2 indexed bycolumn D2. A third index T2.idx.D3 represents the second table T2indexed by column D3. Indices are often provided for facilitating accessto a table in a predetermined order. Each index comprises a sortedcolumn and a row identifier (RID) for identifying rows having a valueindicated by the sorted column. In the present example, table T1 issmall and table T2 has an index on the join column D1. Thus, table T1 isselected as the outer table and table T2 is selected as the inner table.

FIG. 1 c illustrates an exemplary output table TO of the NLJN for thetables shown in FIG. 1 a. For the NLJN, table T1 is selected as theouter and table T2 is selected as the inner table. Thus, for each rowR1, R2, R3, R4, R5, R6 in table T1, every row P1, P2, P3, P4, P5, P6,P7, P8 in table T2 is scanned for a match on C1 and D1 respectively.Thus, for example, the value of C1 for the row R1 of table T1 is a ‘7’.Table T2 is scanned for determining which rows have a value of ‘7’ incolumn D1. As a result of the scan, rows P1, P3, and P7 are returned.Each of these rows P1, P3, P7 are evaluated for determining whether theysatisfy the predicate ‘T2.D2=7 and T2.D3=9’. Only the row P7 satisfiesthe predicate, and thus it is joined with row R1 of table T1 andreturned as an output as illustrated by row S1 of the output table TO.

However, evaluating the predicates for each join outer is bothimpractical and expensive. This is especially true since most databasetables can be very large and processing such tables can be expensive interms of computer resources. Therefore, it is important that techniquesfor joining tables be as efficient as possible. Accordingly, a solutionthat addresses, at least in part, this and other shortcomings isdesired. The need for such a solution has heretofore remainedunsatisfied.

SUMMARY OF THE INVENTION

The present invention satisfies this need, and presents a system, acomputer program product, and an associated method (collectivelyreferred to herein as “the system” or “the present system”) for aninformation retrieval system using index ANDing to improve performancein a database system. It is an advantage of the present system thatconsiderable performs gains can be realized over the prior art byavoiding excessive fetching. In accordance with an aspect of the presentsystem there is provided, for an information retrieval system adapted toprocess a query having an associated plan. The associated plan comprisesapplying predicates, comprising local predicates and a join predicate,to at least two tables, a method of applying the predicates, the methodcomprising using index ANDing to construct a filter for filtering afirst of the tables in accordance with the local predicates; applyingthe join predicate to the tables for determining row identifiers of rowssatisfying the join predicate; using the row identifiers to probe thefilter for determining whether the rows satisfying the join predicatealso satisfy the local predicates; fetching the rows that satisfy boththe join and local predicates; completing the join predicate; andreturning results of the join predicate.

In accordance with another aspect of the present system, there isprovided an information retrieval system adapted to process a queryhaving an associated plan, the information retrieval system for applyingthe predicates, the information retrieval system comprising means forusing index ANDing to construct a filter for filtering a first of thetables in accordance with the local predicates; means for applying thejoin predicate to the tables for determining row identifiers of rowssatisfying the join predicate; means for using the row identifiers toprobe the filter for determining whether the rows satisfying the joinpredicate also satisfy the local predicates; means for fetching the rowsthat satisfy both the join and local predicates; means for completingthe join predicate; and means for returning results of the joinpredicate.

In accordance with yet another aspect of the present system, there isprovided a computer program product having a computer readable mediumtangibly embodying computer executable code for directing an informationretrieval system to apply predicates, the information retrieval systemadapted to process a query having an associated plan, the associatedplan comprises applying predicates, comprising local predicates and ajoin predicate, to at least two tables, the computer program productcomprising code for using index ANDing to construct a filter forfiltering a first of the tables in accordance with the local predicates;code for applying the join predicate to the tables for determining rowidentifiers of rows satisfying the join predicate; code for using therow identifiers to probe the filter for determining whether the rowssatisfying the join predicate also satisfy the local predicates; codefor fetching the rows that satisfy both the join and local predicates;code for completing the join predicate; and code for returning resultsof the join predicate.

In accordance with yet another aspect of the present system, there isprovided an article comprising a computer readable modulated carriersignal being usable over a network, and comprising means embedded in thecomputer readable modulated carrier signal for directing an informationretrieval system to apply predicates, the information retrieval systemadapted to process a query having an associated plan, the associatedplan comprises applying predicates, comprising local predicates and ajoin predicate, to at least two tables, the computer program productcomprising means for using index ANDing to construct a filter forfiltering a first of the tables in accordance with the local predicates;means for applying the join predicate to the tables for determining rowidentifiers of rows satisfying the join predicate; means for using therow identifiers to probe the filter for determining whether the rowssatisfying the join predicate also satisfy the local predicates; meansfor fetching the rows that satisfy both the join and local predicates;means for completing the join predicate; and means for returning resultsof the join predicate.

In accordance with yet another aspect of the system, there is provided,for an information retrieval system adapted to process a query having anassociated plan that applies local predicates to at least two tables, amethod of applying the predicates, comprising constructing a filter forfiltering a first of the tables in accordance with the local predicates.

In accordance with yet another aspect of the system, there is providedan information retrieval system adapted to process a query having anassociated plan that applies local predicates to at least two tables,the information retrieval system for applying the predicates, comprisingmeans for constructing a filter for filtering a first of the tables inaccordance with the local predicates.

In accordance with yet another aspect of the system, there is provided acomputer program product comprising a computer readable medium tangiblyembodying computer executable code for directing an informationretrieval system adapted to process a query having an associated planthat applies local predicates to at least two tables, the informationretrieval system directed to apply the predicates, the computer programproduct comprising code for constructing a filter for filtering a firstof the tables in accordance with the local predicates.

BRIEF DESCRIPTION OF THE DRAWINGS

The various features of the present invention and the manner ofattaining them will be described in greater detail with reference to thefollowing description, claims, and drawings, wherein reference numeralsare reused, where appropriate, to indicate a correspondence between thereferenced items, and wherein:

FIG. 1 a is a set of tables illustrating a query and two exemplarydatabase tables;

FIG. 1 b is a set of tables illustrating three indices for one of thetables shown in FIG. 1 a;

FIG. 1 c is a table illustrating a table representing a nested loop joinof the tables shown in FIG. 1 a;

FIG. 2 is a schematic illustration of an exemplary operating environmentin which an information retrieval system and method using index ANDingof the present invention can be used;

FIG. 3 is a process flow chart illustrating the operation of the queryshown in FIG. 1 a, in accordance with an embodiment of the presentinvention.

DETAILED DESCRIPTION OF PREFERRED EMBODIMENT

The following detailed description of the embodiments of the presentinvention does not limit the implementation of the invention to anyparticular computer programming language. The present invention may beimplemented in any computer programming language provided that theOperating System (OS) provides the facilities that may support therequirements of the present invention. An embodiment is implemented inthe C or C++ computer programming language (or other computerprogramming languages in conjunction with C/C++). Any limitationspresented would be a result of a particular type of operating system orcomputer programming language and would not be a limitation of thepresent invention.

Referring to FIG. 2, there is depicted a data processing system 200comprising a memory 212 for storing a database 202, a relationaldatabase management system (DBMS) 204, a query 206 and a query result207. The DBMS 204 further comprises a query compiler 208 for reading thequery 206 and subsequently generating an executable query 210. Output ofthe executable query 210 is the query result 207. An example of thequery 206 is query (1), discussed in the Background of the Invention.

The DBMS 204 applies or executes the executable query 210 against datastored in the database 202. In query (1), the DBMS 204 applies theexecutable query 210 against the first and second tables T1 and T2illustrated in FIG. 1.

Referring now to FIG. 3, there is depicted a flow chart 300 representinggeneral operation of an executable query 210 for achieving the aboveresult in accordance with an embodiment of the present invention. Theoperation of the executable query 210 comprises several steps S302–S324.

Step S302 comprises starting the executable query 210. In the exemplaryquery (1), there are a plurality of fixed predicates, a conjunctivepredicate and a join predicate. After the executable query 210 isstarted in step S302, step S304 comprises building a Bloom filter forapplying local predicate filtering. Generally, a Bloom filter is afilter bit vector in which values to be filtered are hashed using one ormore hash functions to set multiple bit positions in the filter bitvector. Given a Bloom filter and a value to be tested, the filterindicates that either the value may be present in the filter or that thevalue is definitely not present in the filter. A bit vector for eachfixed predicate is built by scanning a corresponding index, ifavailable. The bit vectors comprise a ‘1’ for each row in the tableindicating a possible match with the predicate and a ‘0’ for each row inthe table indicating a definite mismatch. A master Bloom filter vectoris created for facilitating execution of the conjunctive predicate bylogically combining the bit vectors for each fixed predicate inaccordance with the conjunctive predicate.

Step S306 causes one row to be fetched from the outer table for joiningwith any rows from the inner table that are found to have matching joincolumns, as is be described in the following steps. In Step S308, acheck is performed to determine if a row was obtained from the outertable. If no row was obtained, then the query 206 is complete (stepS309). Otherwise, execution proceeds to step S310.

Step S310 comprises beginning to apply the join predicate of query (1)to the appropriate rows of the inner table. For each row in the outertable, all of the rows of the inner table are scanned. As previouslydescribed, the inner table generally has an index on the join column.Therefore, scanning of the inner table is relatively efficient, as therequired value in the join column is simply looked up in the index. Instep S312, if one or more matches are found for a given row, the RIDsfor the matching rows are returned and the operation proceeds to stepS314. Otherwise, the join proceeds to step S306, obtaining the next rowin the outer table.

Step S314 comprises using the RIDs returned from the index of the joincolumn for probing the master Bloom filter. The Bloom filter is probedto determine if each RID matching the row of the outer table is present.In step 316, if the result of the probe is a ‘1’, then the value may bepresent and the operation proceeds to step S318. If the result of theprobe is a ‘0’, then the value is definitely not present and theoperation proceeds to step S310 to process the next RID from the innertable's join predicate match. These steps are repeated until allqualifying RIDs have been used to probe the Bloom filter.

As previously described, the Bloom filter results in a definite responseonly when determining the absence of a value, not the presence of avalue. Therefore, step S318 causes the row from the inner table to befetched. Consequently, all of the necessary columns are available toverify that the row qualifies all predicates. Step 320 comprisesverifying the result of the probe for the given RID from step S314.Thus, the predicates are applied to the row represented by the RID toverify that the row, in fact, does satisfy the query 206. In step S322,if the row is a valid match, it is joined with the row from the outertable in step SS324 and returned as a row in the output table. In eithercase, if the row does not qualify the predicates, or if the row isreturned as a row in the output table, the operation proceeds to stepS310, where the next RID is obtained for the inner table and theoperation proceeds as described above.

Details of the operation of an executable query 210 in accordance withthe present embodiment, query (1), and the tables illustrated in FIG. 1a, may now be described with reference to FIG. 3. As described withreference to the prior art, the join performed is a NLJN, where table T1is the outer table and table T2 is the inner table.

Step S302 starts the executable query 210.

Step S304 comprises building a Bloom filter for applying local predicatefiltering. In the present example, the local, fixed predicates of query(1) are ‘T2.D2=7’ and ‘T2.D3=9’. For the first fixed predicate‘T2.D2=7’, the index T2.idx.D2 of table T2 is accessed. Since the indexT2.idx.D2 is organized in accordance with the value of D2, the predicatecan be evaluated relatively quickly. A first bit vector is built for thefirst fixed predicate. If the predicate is true for a given RID then anassociated bit, determined in by hashing the RID, is set to ‘1’,otherwise it is set to ‘0’.

For the second fixed predicate ‘T2.D3=9’ of query (1), the indexT2.idx.D3 of table T2 is accessed. Since the index T2.idx.D3 isorganized in accordance with the value of D3, the predicate can beevaluated relatively quickly. As each RID is read from the index andhashed, a corresponding bit is combined with the first bit vector. Thatis, if the row associated with the RID satisfies the second predicateand the corresponding bit in the bit vector generated with the firstpredicate is a ‘1’, then the two bits are effectively ANDed together.The corresponding bit in the new bit vector associated with the secondpredicate is set to a ‘1’. If the row from the second predicateassociated with the RID does not satisfy the predicate, then a ‘0’ iseffectively ANDed with a corresponding bit in the bit vector generatedusing the first predicate by not setting a bit in the bit vectorassociated with the second predicate. Similarly, if the row from thesecond predicate associated with the RID does satisfy the predicate butthe corresponding bit in the bit vector generated using the firstpredicate is a ‘0’, then again, the two bits are effectively ANDedtogether by not setting a bit in the bit vector associated with thesecond predicate. The resulting bit vector is a master bit vectorrepresenting the conjunctive predicate ‘T2.D2=7 AND T2.D3=9 ’.

Step S306 comprises fetching the first row R1 from table T1. Step S308determines if a row was fetched from table T1. Since a row was fetched,the operation proceeds to step S310, which comprises starting to applythe join predicate, ‘T1.C1=T2.D1’. The value of C1 in the first row R1of table T1 is ‘7’. Analyzing index T2.idx.D1 reveals that RIDs 1, 3,and 7 each have a value of ‘7’ in column D1. Step S312 determines that aRID qualified the join predicate. Accordingly, in step S314, the Bloomfilter is probed with RID 1. Since D2≠7, the probe may likely return a‘0’, causing the operation to proceed through step S316 back to stepS310 where the next RID is retrieved and used for probing the Bloomfilter in step S314. The next RID, RID 3, also likely returns a ‘0’since neither D2≠7 nor D3≠9. The next RID, RID 7, returns a ‘1’ sinceboth D2=7 and D3=9. Consequently, the operation proceeds to step S318.

In step 318, the inner row is fetched from the table. In step S320, thepredicates are applied to the row represented by RID 7. Since D1=7,D2=7, and D3=9, the predicate is verified causing step S322 to continuethe operation to step S324 where the row represented by RID 7 is joinedwith the first row R1 in table T1 providing a first output row S1 inoutput table TO. The operation continues to step S310. In step S312, itis then determined that RID 7 is the last RID for rows P1, P2, P3, P4,P5, P6, P7, P8 in the inner table T2 matching the first row R1 in theouter table T1. Accordingly, the join proceeds to step S306, where thenext row, R2, in the outer table T1 is fetched.

The value of C1 in the second row R2 of table T1 is ‘3’. Analyzing indexT2.idx.D1 reveals that RIDs 5 and 6 each have a value of ‘3’ in columnD1. Accordingly, in step S314, the Bloom filter is probed with RID 5.Since D2≠7, the probe may likely return a ‘0’ and the next RID is usedfor probing the Bloom filter. The next RID, RID 6, returns a ‘1’ sinceboth D2=7 and D3=9. As a result, the operation proceeds to step S318.

Step 318 applies the predicates to the row represented by RID 6. SinceD1=3, D2=7 and D3=9, the predicate is verified and the row representedby RID 6 is joined with the second row R2 in table T1 providing a secondoutput row S2 in output table TO. The operation then returns to stepS310 and step S312, where it is determined that RID 6 is the last RIDfor rows P1, P2, P3, P4, P5, P6, P7, P8 in the inner table T2 matchingthe second row R2 in the outer table T1. Accordingly, the join proceedsto the next row in the outer table T1 and returns to step S306. Theoperation continues until all rows R1, R2, R3, R4, R5, R6 in the outertable T1 have been processed, the result of which is shown in FIG. 1 c.

Accordingly, the present invention provides considerable performancegains on the prior art by reusing the Bloom filter built from localpredicates, without requiring covering indices on the combination ofjoin and local predicated columns. Although the present embodiment isdescribed with reference to the use of a Bloom filter, it may beapparent to a person skilled in the art that other filtering techniquesfor implementing index ANDing may be applied for achieving the desiredresult.

In an alternate embodiment, the NLJN is an outer join. An outer joincauses all rows from the outer to be preserved, even if they do not finda match on the inner table. In this embodiment, steps S316 and S322 bothreturn the outer row padded with NULLs to provide a row for the outputtable.

In yet an alternate embodiment, the inner rows are fetched and thepredicates verified after the join has been completed. In thisembodiment, step S324 precedes steps S318, and a new step is added todiscard rows that fail the verification steps S320 and S322.

In yet an alternate embodiment, instead of using a filtered index forperforming a join, as described in the previous embodiments, anunfiltered index is used for providing the table in a sorted order. Aquery plan is provided for sorting and applying local predicates to atable and joining the table to another table. However, it is expensiveto sort the entire table and then apply the predicates for filtering thetable as required. Therefore, if an index is available that provides auseful sorting order, or a desirable level of clustering, as determinedby an optimizer, it is possible to exploit index ANDing for filteringthe table and avoiding excessive fetching for executing the query.

“The present embodiment begins in a manner similar to the previousembodiment with steps S302–S316. However, at step S317 that follows stepS316, a new Bloom filter is built, setting only bits that representpotential match rows from the inner table. Once all outer rows have beenprocessed, the new Bloom filter is designated as the master Bloomfilter. The index that provides the useful order is then used to probethe new master Bloom filter. As in the previous embodiment, stepsS318–S322 are performed for each RID in the index that corresponds to‘1’ in the master Bloom filter and the predicates are applied to verifythe result. If the result is verified, the row is returned. If a RIDcorresponds to a ‘0’ in the Bloom filter, the row does not satisfy thepredicate and the next RID is analyzed. Since the Bloom filter is probedin the order of the index, the output is provided in the order of theindex, thus providing an output having a useful order while avoidingexcessive fetching from the inner table.”

In yet another alternate embodiment, a semijoin is used for combiningtables where a first table has an index with a useful order but no localpredicates and a second table has local predicates and a join predicateto the table with the useful order. As described in the previousembodiment, a filtering table is constructed for filtering the secondtable. A semijoin is used for populating a Bloom filter with thefiltering table. The index on the first table that has the useful orderis then used to probe the Bloom filter and provide the order desired.

One skilled in the art may appreciate that all references above to theexecution of a single query can be considered in the context ofexecution of a single join within the context of a larger querycomprising many joins. Output rows from this join methodology would beprocessed by addition joins that may or may not use the methodsdescribed herein.

Though the above embodiments are described primarily with reference to amethod aspect of the invention, the invention may be embodied inalternate forms. In an alternative aspect, there is provided a computerprogram product having a computer-readable medium tangibly embodyingcomputer executable instructions for directing a data processing systemto implement any method, query compiler, or executable query aspreviously described above. It may be appreciated that the computerprogram product may be a floppy disk, hard disk or other medium for longterm storage of the computer executable instructions.

In yet an alternative aspect, there is provided an article having acomputer-readable signal-bearing medium, and having means in the mediumfor directing a data processing system to implement any method, querycompiler, or executable query as previously described above. It may beappreciated that a supplier of the compiler may upload the article to anetwork (such as the Internet) and users may download the article viathe network to their respective data processing systems.

It may be appreciated that variations of some elements are possible toadapt the invention for specific conditions or functions. The conceptsof the present invention can be further extended to a variety of otherapplications that are clearly within the scope of this invention.

It is to be understood that the specific embodiments of the inventionthat have been described are merely illustrative of certain applicationof the principle of the present invention. Numerous modifications may bemade to an information retrieval system and method using index ANDingfor improving performance invention described herein without departingfrom the spirit and scope of the present invention.

What is claimed is:
 1. A processor-implemented information retrievalmethod for processing a query having an associated plan, the associatedplan comprises applying predicates, comprising local predicates and ajoin predicate, to at least two tables, the method comprising: usingindex ANDing to construct a master filter for filtering a first table inaccordance with the local predicates; iteratively fetching a row from asecond table; iteratively applying the join predicate to the first tableusing values from the fetched row from the second table for determiningrow identifiers of rows in the first table satisfying the joinpredicate; using the row identifiers to probe the master filter fordetermining whether the rows satisfying the join predicate also satisfythe local predicates; fetching the rows from the first table thatsatisfy both the join and local predicates resulting from the probing ofthe master filter; completing the join predicate of the rows fetchedfrom the first of the table; and returning results of the join predicateby combining the rows fetched from the first table with the rows fetchedfrom the second table.
 2. The method of claim 1, wherein the use of therow identifiers to probe the filter for determining whether the rowssatisfying the join predicate satisfy the local predicates beforefetching the rows reduces excessive fetching for achieving a desiredoutput.
 3. The method of claim 1, wherein the filter is a Bloom filter.4. The method of claim 3, further comprising applying the predicates toeach row that satisfies both the join predicate and the local predicatesfor verification before returning the results.
 5. The method of claim 4,wherein the join predicate uses a nested loop join.
 6. The method ofclaim 5, wherein the join is an outer join and all rows on an outertable are preserved even if a match on a corresponding inner table isnot found.
 7. The method of claim 4, wherein applying the predicates toeach row that satisfies both the join predicate and the local predicatesfor verification is performed before completing the join predicate. 8.The method of claim 4, wherein applying the predicates to each row thatsatisfies both the join predicate and the local predicates forverification is performed after completing the join predicate.
 9. Themethod of claim 1, wherein the index with the sorting order providesclustering of rows according to a request by an optimizer.
 10. Aprocessor-implemented information retrieval system adapted to process aquery having an associated plan, the associated plan comprises applyingpredicates that comprise local predicates and a join predicate, to atleast two tables, the information retrieval system comprising: means forusing index ANDing to construct a master filter for filtering a firsttable in accordance with the local predicates; means for iterativelyfetching a row from a second table; means for iteratively applying thejoin predicate to the first table using values from the fetched row fromthe second table for determining row identifiers of rows in the firsttable satisfying the join predicate; means for using the row identifiersto probe the master filter for determining whether the rows satisfyingthe join predicate also satisfy the local predicates; means for fetchingthe rows from the first table that satisfy both the join and localpredicates resulting from the probing of the master filter; means forcompleting the join predicate of the rows fetched from the first of thetable; and means for returning results of the join predicate predicateby combining the rows fetched from the first table with the rows fetchedfrom the second table.
 11. The information retrieval system of claim 10,wherein the means for using the row identifiers to probe the filter fordetermining whether the rows satisfying the join predicate satisfy thelocal predicates before fetching the rows reduces excessive fetching forachieving a desired output.
 12. The information retrieval system ofclaim 10, wherein the filter is a Bloom filter.
 13. The informationretrieval system of claim 12, further comprising means for applying thepredicates to each row that satisfies both the join predicate and thelocal predicates for verification.
 14. The information retrieval systemof claim 13, wherein the join predicate uses a nested loop join.
 15. Theinformation retrieval system of claim 14, wherein the join predicatesuses an outer join and all rows on an outer table are preserved even ifa match on a corresponding inner table is not found.
 16. The informationretrieval system of claim 13, wherein the means for applying thepredicates to each row that satisfies both the join predicate and thelocal predicates for verification applies the predicates before the joinpredicate is completed.
 17. The information retrieval system of claim13, wherein the means for applying the predicates to each row thatsatisfies both the join predicate and the local predicates forverification applies the predicates after the join predicate iscompleted.
 18. The information retrieval system of claim 10, wherein theindex with the sorting order provides clustering of rows as requested byan optimizer.
 19. A computer program product having executableinstruction codes stored on a computer-usable medium for directing aninformation retrieval system to apply predicates, the informationretrieval system processing a query having an associated plan, and theassociated plan comprises applying predicates that comprises localpredicates and a join predicate, to at least two tables, the computerprogram product comprising: a set of instruction codes for using indexANDing to construct a master filter for filtering a first table inaccordance with the local predicates; a set of instruction codes foriteratively fetching a row from a second table; a set of instructioncodes for iteratively applying the join predicate to the first tableusing values from the fetched row from the second table for determiningrow identifiers of rows in the first table satisfying the joinpredicate; a set of instruction codes for using the row identifiers toprobe the master filter for determining whether the rows satisfying thejoin predicate also satisfy the local predicates; a set of instructioncodes for fetching the rows from the first table that satisfy both thejoin and local predicates resulting from the probing of the masterfilter; a set of instruction codes for completing the join predicate ofthe rows fetched from the first of the table; and a set of instructioncodes for returning results of the join predicate by combining the rowsfetched from the first table with the rows fetched from the secondtable.
 20. The computer program product of claim 19, wherein the set ofinstruction codes for using the row identifiers to probe the filter,satisfies the local predicates before fetching the rows reducesexcessive fetching for achieving a desired output.
 21. The computerprogram product of claim 19, wherein the filter is a Bloom filter. 22.The computer program product of claim 21, further comprising a set ofinstruction codes for applying the predicates to each row that satisfiesboth the join predicate and the local predicates for verification. 23.The computer program product of claim 22, wherein the join predicateuses a nested loop join.
 24. The computer program product of claim 23,wherein the join predicates uses an outer join and all rows on an outertable are preserved even if a match on a corresponding inner table isnot found.
 25. The computer program product of claim 22, wherein the setof instruction code for applying the predicates to each row thatsatisfies both the join predicate and the local predicates, applies thepredicates to each row that satisfies both the join predicate and thelocal predicates for verification before the join predicate iscompleted.
 26. The computer program product of claim 22, wherein the setof instruction code for applying the predicates to each row thatsatisfies both the join predicate and the local predicates, applies thepredicates to each row that satisfies both the join predicate and thelocal predicates for verification after the join predicate is completed.27. The computer program product of claim 19, wherein the index with thesorting order provides clustering of rows as requested by an optimizer.